package com.yw.atp.service;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.yw.atp.model.Command;
import com.yw.atp.model.Staff;


@Repository
@Transactional
public class StaffService{
	@PersistenceContext
    private EntityManager em;
	
	public List<Staff> getStaffs(Staff staff,String pName){
		Query query = em.createQuery("select s FROM  Staff s ,Profession p where s.name like :name and s.mobile like :mobile and s.department.name like :dname and p.name like :pname and p in elements(s.professions) and s.status !='1' order by s.id desc");
		String name   = getValue(staff.getName());
		String mobile = getValue(staff.getMobile());
		String dname  = staff.getDepartment()!=null?getValue(staff.getDepartment().getName()):"";
		String pname  = pName!=null?pName:"";
		query.setParameter("name","%"+name+"%");
		query.setParameter("mobile","%"+mobile+"%");
		query.setParameter("dname","%"+dname+"%");
		query.setParameter("pname","%"+pname+"%");
		return (List<Staff>)query.getResultList();
	}
	
	public List<Staff> getStaffs(Command command){
		Query query = em.createQuery("select s FROM  Staff s,Command c where (s in elements(c.staffList) or s.department in elements(c.deparList) or exists (select p from Profession p where p in  elements(s.professions) and p in elements(c.proList))) and c.id = :commID order by s.id desc");
		query.setParameter("commID",command.getId());
		return (List<Staff>)query.getResultList();
	}
	
	public Object getStaffsCount(Command command){
		Query query = em.createQuery("select count(*) FROM  Staff s,Command c where (s in elements(c.staffList) or s.department in elements(c.deparList) or exists (select p from Profession p where p in  elements(s.professions) and p in elements(c.proList))) and c.id = :commID order by s.id desc");
		query.setParameter("commID",command.getId());
		return (Object)query.getSingleResult();
	}
	
	public Object getReplyCount(Command command){
		Query query = em.createNativeQuery("select count(*) from (select r.owner_id from yw_reply r where r.command_id = :commID group by r.owner_id) c ");
		query.setParameter("commID", command.getId());
		return (Object)query.getSingleResult();
	}
	
	public String getValue(String str){
		if(str==null){
			return "";
		}
		return str;
	}
	
	public Staff findByMobile(String mobileID){
		Query query = em.createQuery("select s FROM  Staff s where s.mobileID = :mobileID  and s.status !='1'");
		query.setParameter("mobileID",mobileID);
		List<Staff> slist = (List<Staff>)query.getResultList();
		if(slist!=null&&slist.size()>0){
			return slist.get(0);
		}
		return null;
	}
}
